package dao;

import dto.Repair.RepairListResp;
import dto.RepairDetail.DetailDate;
import dto.RepairDetail.RepairDetailResp;
import dto.RepairListReq;
import util.DatasourceUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Kevin
 * @date 2020-12-29 20:11
 */
public class RepairDao {

    private static final String COUNT = "count(*)";

    private static final int ZERO = 0;

    /**
     * 查询故障列表
     * @param req 求情体
     * @return 响应
     * @throws SQLException SQL异常
     */
    public RepairListResp repairList(RepairListReq req) throws SQLException {
        RepairListResp rlr = new RepairListResp();
        try( Connection connection = DatasourceUtil.getConnection() ) {
            ResultSet r1 ;
            ResultSet r2 ;


            StringBuilder sql = new StringBuilder();
            StringBuilder sql1 = new StringBuilder();
            StringBuilder sql2 = new StringBuilder();

            sql.append(" \n FROM `repair_master` m, `user` u \n WHERE m.`name` = u.username ");
            sql1.append(" SELECT  m.`id`,m.`date`,m.`clazz`,m.`room`,m.`type`,m.`reason`,u.`name`,m.`state` ");
            sql2.append(" SELECT count(*)  ");

            // 保存占位符对应的对象
            List<Object> list = new ArrayList<>();
            // 查询 clazz
            if( req.getClazz() != null && req.getClazz().length != ZERO){

                sql.append("\n and m.clazz in ( " );
                for (int i = 0 ;i < req.getClazz().length; i++) {
                    sql.append( " ? " );
                    list.add( req.getClazz()[i] );
                    if( i != req.getClazz().length-1 ) {
                        sql.append(",");
                    }
                }
                sql.append( "   ) ");
            }
            // 模糊查询 room , 把匹配的字符串转为大写
            if( req.getRoom() != null && req.getRoom().length != ZERO){

                sql.append("\n and ( m.room like  " );
                for (int i = 0 ;i < req.getRoom().length; i++) {
                    if( i != 0 ) {
                        sql.append(" or m.room like ");
                    }
                    sql.append( " ? " );
                    list.add( "%"+req.getRoom()[i].toLowerCase()+"%" );
                }
                sql.append( "   ) ");
            }
            // 查询type
            if(req.getType() != null && req.getType().length != ZERO){

                sql.append("\n and m.type in ( " );
                for (int i = 0 ;i < req.getType().length; i++) {
                    sql.append( " ? " );
                    list.add( req.getType()[i] );
                    if( i != req.getType().length-1 ) {
                        sql.append(",");
                    }
                }
                sql.append( "   ) ");
            }
            // 模糊查询 name
            if(req.getName()!=null && req.getName().length != ZERO){

                sql.append("\n and ( u.name like  ? " );
                list.add("%"+req.getName()[0]+"%");
                for (int i = 1 ;i < req.getName().length; i++) {
                    sql.append(" or u.name like ? ");
                    list.add( "%"+req.getName()[i]+"%" );
                }
                sql.append( "   ) ");
            }
            // 查询 实体类：status---数据库：state
            if( req.getStatus() != null && req.getStatus().length != ZERO){

                sql.append("\n and m.state in ( " );
                for (int i = 0 ;i < req.getStatus().length; i++) {
                    sql.append( " ? " );
                    list.add( req.getStatus()[i] );
                    if( i != req.getStatus().length-1 ) {
                        sql.append(",");
                    }
                }
                sql.append( "   ) ");
            }
            // 查询 时间段 startTime~endTime
            if( req.getStartTime() != null || req.getEndTime() != null ){
                String start = "2018-01-01";
                String end = "9999-01-01";
                // 至于 start > end 这种返回为空的就不管它了
                if( req.getStartTime() != null ) {
                    start = req.getStartTime();
                }
                if( req.getEndTime() != null ) {
                    end = req.getEndTime();
                }
                sql.append("\n and  m.date between ? and ? ");
                list.add( start );
                list.add( end );
            }

            sql.append("\n ORDER BY `date` DESC ");
            // 得到  计算查询结果总数 的 sql 查询语句
            sql2.append( sql );
            Object[] params2 = list.toArray();
            r2 = DatasourceUtil.executeQuery(connection,sql2.toString(),params2);

            // 把结果集 r1 封装进对象，如果它不为空 且 查询起点不超过查询总条数的话
            if( r2.next() && r2.getInt(COUNT) != 0   ){
                // 查询分页结果，使参数合理化
                int num = 1;
                int size = 10;
                if( req.getPageNum() != null && req.getPageNum() > 1   ) {
                    num = req.getPageNum();
                }
                if( req.getPageSize() != null && req.getPageSize() > 0 ) {
                    size = req.getPageSize();
                }
                // 如果起始查询数小于总数，则执行查询，否则不执行（因为结果集为空）
                if(  (num-1) * size <= r2.getInt(COUNT) ) {
                    sql.append( "\n limit  ? , ? ; \n" );
                    list.add( (num-1) * size );
                    list.add( size );
                    //根据 SQL获取 结果集
                    sql1.append( sql.toString() );
                    Object[] params = list.toArray();
                    r1 = DatasourceUtil.executeQuery(connection,sql1.toString(),params);
                    // 把结果集 r1 中的值封装进对象
                    rlr.setRespData(r1, num, size);
                }
                // 把起始页码 和 页面大小封装进对象
                rlr.setPageNum(num);
                rlr.setPageSize(size);
            }
            // 把结果集 r2 中的值封装进对象
            rlr.setPageTotal( r2.getInt(COUNT) );
        }
        return rlr;
    }

    /**
     * 查询故障列表详情
     * @param repairId 维修单id
     * @return 响应对象
     * @throws SQLException SQL查询异常
     */
    public RepairDetailResp repairDetail(Integer repairId) throws SQLException {

        RepairDetailResp rd = null;

        String str1 = "SELECT m.`id`,m.`date`,m.`clazz`,m.`room`,m.`type`,m.`reason`, u.`name` ,m.`state` \n" +
                "FROM `repair_master` AS m\n" +
                "INNER JOIN `user` AS u\n" +
                "ON m.`name` = u.`username`\n" +
                "WHERE id = ?";
        // 从降序改为升序，删除了DESC
        String str2 = "SELECT * FROM `repair_info` WHERE `repair` = ? ORDER BY `date`  ";
        Integer[] params = new Integer[]{ repairId };

        try(   Connection connection = DatasourceUtil.getConnection() ) {
            // 将结果集 rs1 的内容封装进对象
            ResultSet rs1 = DatasourceUtil.executeQuery(connection,str1,params );

            if( rs1.next() ){
                rd = new RepairDetailResp(rs1);
                // 将结果集 rs2 的内容封装进对象
                ResultSet rs2 = DatasourceUtil.executeQuery(connection,str2,params);
                while( rs2.next() ){
                    DetailDate detail = new DetailDate( rs2 );
                    queryRepairPerson(connection,detail);
                    rd.getRepairRecord().add(detail);
                }
            }
        }
        return rd;
    }

    /**
     * 根据维修记录ID，查询相关的维修人员名字
     * @param connection 数据库连接对象
     * @param detail    一条详细维修记录
     * @throws SQLException SQL异常
     */
    public void queryRepairPerson(Connection connection,DetailDate detail) throws SQLException {
        String str = "SELECT u.`name`\n" +
                "FROM `repair_person` AS p \n" +
                "INNER JOIN `user` AS u \n" +
                "ON p.`name` = u.`username`\n" +
                "WHERE p.`info` = ? ";

        ResultSet rs3 = DatasourceUtil.executeQuery(connection,str,new Integer[]{detail.getId()} );
        while( rs3.next() ){
            detail.getNames().add( rs3.getString("name") );
        }
    }


}
















